/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBSupport;

import DataStructures.Bug;
import DataStructures.BugData;
import DataStructures.Metrics;
import DataStructures.Project;
import DataStructures.ProjectInformation;
import DataStructures.RepositoryData;
import General.Globals;
import MetricsRetriever.RetrieverComponents.Matcher;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import org.tmatesoft.svn.core.SVNLogEntry;
import org.tmatesoft.svn.core.SVNLogEntryPath;

/**
 *
 * @author Manuel
 */
public class DBApi {

    QueryExecutor exec;

    //build a new API with DB connection through the QueryExecutor
    public DBApi(){
        exec = new QueryExecutor();
    }

    //check all links and add them to the database if not yet existant
    public boolean insertNewLinks(ArrayList<String> links){

        ArrayList<String> DBLinks = exec.query("SELECT url FROM link");
        if(DBLinks == null)
            return false;
        String currentLink;
        for (int i = 0;i<links.size();i++){
            currentLink = links.get(i);
            if(!DBLinks.contains(currentLink)){
                String forge = (currentLink.contains("code.google.com")) ? "googlecode" : "sourceforge";
                exec.insert("INSERT INTO Link (url,forge,status,lastupdate) VALUES ('"+escape(currentLink)+"','"+forge+"','new','now');");
            }
        }
        return true;

    }

    //returns all links which have interesting projects connected to them or are new
    public ArrayList<String> getAllInterestingLinks(){

        String query = "SELECT url FROM link WHERE status='new' OR (current_date - lastupdate > "+Globals.UPDATE_INTERVAL+" AND (status='good' OR status='tocheck'));";
        ArrayList<String> links = exec.query(query);
        return links;

    }


    public boolean updateProject(Project p){
        long timetaken = System.currentTimeMillis();
        boolean success = false;
        ArrayList<String> result = exec.query("SELECT project_id FROM project WHERE url='"+p.getProjectInfo().getUrl()+"';");
        boolean exists = !(result == null || result.isEmpty() || result.get(0) == null || result.get(0).toLowerCase().equals("null"));
        if(exists)
            System.out.println("Project "+p.getProjectInfo().getUrl()+ " exists "+result.get(0));
        boolean isToUpdate = true;

        // if the project already exists in the database, and is not bad (e.g. no more active), check whether has to be updated
        if(exists && (p.getStatus() != Globals.BAD_PROJECT)){
            //first check whether the project has been already updated recently or has to be updated
            String pID = result.get(0);
           /*String lastUpdateString = exec.query("SELECT lastupdate FROM link WHERE url='"+p.getProjectInfo().getUrl()+"';").get(0);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            //if no data is given, project was never updated -> updated at time 0
            Date lastUpdate = new Date(0);
            try {
                lastUpdate = sdf.parse(lastUpdateString);
                long difference = System.currentTimeMillis() - lastUpdate.getTime();
                System.out.println("DIFFERENCE IS: "+difference);
                //if the project has recent data, do not update it -> return success
                if(difference < Globals.UPDATE_INTERVAL){
                    System.out.println("MUST NOT BE UPDATED");
                    return true;
                }
                //ADD NEW INFORMATION TO EXISTING PROJECT
            } catch (ParseException ex) {
                ex.printStackTrace();
            }
*/
            //first update the link, according to its status
            if(p.getStatus()== Globals.BAD_PROJECT){
                String update = "UPDATE link SET status='bad', lastupdate='now' WHERE url='"+p.getProjectInfo().getUrl()+"';";
                exec.insert(update);
            }
            else if(p.getStatus() == Globals.TOCHECK_PROJECT){
                String update = "UPDATE link SET status='tocheck', lastupdate='now' WHERE url='"+p.getProjectInfo().getUrl()+"';";
                exec.insert(update);
            }
            else if(p.getStatus() == Globals.GOOD_PROJECT){
                String update = "UPDATE link SET status='good', lastupdate='now' WHERE url='"+p.getProjectInfo().getUrl()+"';";
                exec.insert(update);

                //since the project needs updates and is good, update collaborator,release,revision,commit and bug data
                
                this.insertCollaborators(p.getProjectInfo(), pID);

                //if there is a new release, store its data
                long releaseDifference = System.currentTimeMillis() - p.getProjectInfo().getArchieveLink().getDate().getTime();
                if(releaseDifference < Globals.UPDATE_INTERVAL){
                    this.insertReleaseData(p.getCodeMetrics(), pID);
                }

                //if there is a new revision, store its data (check inside the method)
                this.insertRevisionData(p.getRepositoryData(),pID, p.getMatcher());

                this.insertBugs(p.getBugData(), pID,p.getMatcher());

                //***********************C**ONTINUE * HERE *****************

            }




        }

        //if the project does not exist in the database or it has to be updated check if it is good and eventually store/update its data
        else if(!exists)
        {

            if(p.getStatus()== 0){
                p.setStatus(Globals.BAD_PROJECT);
            }

            if(p.getStatus()== Globals.BAD_PROJECT){
                String update = "UPDATE link SET status='bad', lastupdate='now' WHERE url='"+p.getProjectInfo().getUrl()+"';";
                exec.insert(update);
            }
            else if(p.getStatus() == Globals.TOCHECK_PROJECT){
                String update = "UPDATE link SET status='tocheck', lastupdate='now' WHERE url='"+p.getProjectInfo().getUrl()+"';";
                exec.insert(update);
            }
            else if(p.getStatus() == Globals.GOOD_PROJECT){
                String update = "UPDATE link SET status='good', lastupdate='now' WHERE url='"+p.getProjectInfo().getUrl()+"';";
                exec.insert(update);

                //insert the new project information if the project does not exist
                if(!this.insertProjectInformation(p.getProjectInfo()))
                    return false;
                String pID = exec.query("SELECT project_id FROM project WHERE url='"+p.getProjectInfo().getUrl()+"';").get(0);

                //insert the release related data in a statement
                if(!this.insertReleaseData(p.getCodeMetrics(),pID)){
                    exec.insert("DELETE FROM project where project_id='"+pID+"';");
                    System.out.println("COULD NOT INSERT METRICS FOR "+p.getProjectInfo().getUrl());
                    return false;
                }

                //insert the gathered collaborator data in a transaction
                if(!insertCollaborators(p.getProjectInfo(),pID)){
                    exec.insert("DELETE FROM project where project_id='"+pID+"';");
                    return false;
                }

                //insert the gathered bug data in a transaction
                this.insertBugs(p.getBugData(), pID,p.getMatcher());

                //insert the gathered revision and commit data in a transaction
                if(!this.insertRevisionData(p.getRepositoryData(),pID,p.getMatcher())){
                    exec.insert("DELETE FROM project where project_id='"+pID+"';");
                }

            }

        }
        System.out.println("S"+p.getStatus() +" "+(System.currentTimeMillis()-timetaken) +" for project "+p.getProjectInfo().getUrl());

        return success;

    }

    private boolean insertProjectInformation(ProjectInformation info){
        boolean success = false;

        String labels = "";
        for(int i = 0;i<info.getLabels().size();i++){
                    labels += info.getLabels().get(i)+",";
        }
        labels = labels.equals("") ? "NULL" : escape(labels);
        String language = info.getLanguageChosen().equals("java") ? "java" : "cpp";
        String repositoryLink = info.getRepositoryLink().equals("") ? "NULL" : "'"+escape(info.getRepositoryLink())+"'";
        String description = info.getDescription().equals("") ? "NULL" : "'"+escape(info.getDescription()).trim().replaceAll(" +", " ")+"'";

        String insertProject = "INSERT INTO project (name,url,description,labels,language,repositoryLink,activity)"
                        + " VALUES ('"+escape(info.getName())+"','"+info.getUrl()+"','"+escape(description)+"','"+escape(labels)+"','"+escape(language)+"',"+repositoryLink+",'"+info.getActivity()+"');";
        success = exec.insert(insertProject);
        return success;

    }


    private boolean insertReleaseData(HashMap metricsMap,String projectID) {
        boolean success = false;
        Metrics codeMetrics = (Metrics) metricsMap.get(".project.");

        String wmc = (codeMetrics.getWmc() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getWmc())+"'";
        String dit = (codeMetrics.getDit() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getDit())+"'";
        String cbo = (codeMetrics.getCbo() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getCbo())+"'";
        String noc = (codeMetrics.getNoc() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getNoc())+"'";
        String rfc = (codeMetrics.getRfc() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getRfc())+"'";
        String lcom = (codeMetrics.getLcom() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getLcom())+"'";
        String fanin = (codeMetrics.getFanin() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getFanin())+"'";
        String fanout = (codeMetrics.getFanout() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getFanout())+"'";

        String maxwmc = (codeMetrics.getMaxwmc() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMaxwmc())+"'";
        String maxdit = (codeMetrics.getMaxdit() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMaxdit())+"'";
        String maxcbo = (codeMetrics.getMaxcbo() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMaxcbo())+"'";
        String maxnoc = (codeMetrics.getMaxnoc() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMaxnoc())+"'";
        String maxrfc = (codeMetrics.getMaxrfc() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMaxrfc())+"'";
        String maxlcom = (codeMetrics.getMaxlcom() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMaxlcom())+"'";
        String maxfanin = (codeMetrics.getMaxfanin() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMaxfanin())+"'";
        String maxfanout = (codeMetrics.getMaxfanout() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMaxfanout())+"'";

        String minwmc = (codeMetrics.getMinwmc() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMinwmc())+"'";
        String mindit = (codeMetrics.getMindit() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMindit())+"'";
        String mincbo = (codeMetrics.getMincbo() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMincbo())+"'";
        String minnoc = (codeMetrics.getMinnoc() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMinnoc())+"'";
        String minrfc = (codeMetrics.getMinrfc() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMinrfc())+"'";
        String minlcom = (codeMetrics.getMinlcom() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMinlcom())+"'";
        String minfanin = (codeMetrics.getMinfanin() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMinfanin())+"'";
        String minfanout = (codeMetrics.getMinfanout() == -1d) ? "NULL" : "'"+Double.toString(codeMetrics.getMinfanout())+"'";
        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");
        String date = escape(sdf.format(codeMetrics.getReleaseDate()));
        if(wmc.equals("NULL") || dit.equals("NULL") || cbo.equals("NULL")){
            return false;
        }
        String insertRelease = "INSERT INTO release (date,wmc,minwmc,maxwmc,dit,mindit,maxdit,rfc,minrfc,maxrfc,lcom,minlcom,maxlcom,noc,minnoc,maxnoc,cbo,mincbo,maxcbo,fanin,minfanin,maxfanin,project_id) VALUES ("
                +"'"+date+"',"+wmc+","+minwmc+","+maxwmc+","+dit+","+mindit+","+maxdit+","+rfc+","+minrfc+","+maxrfc+","+lcom+","+minlcom+","+maxlcom+","+noc+","+minnoc+","+maxnoc+","+cbo+","+mincbo+","+maxcbo+","+fanin+","+minfanin+","+maxfanin+",'"+projectID+"');";
        success = exec.insert(insertRelease);
        String queryRelease = "SELECT release_id FROM release WHERE project_id='"+projectID+"' AND date='"+date+"';";
        String releaseID = exec.query(queryRelease).get(0);
        metricsMap.remove(".project.");
        ArrayList classes = new ArrayList(metricsMap.values());

        //insert data of every single class
        for(int i = 0;i<classes.size();i++){

            Metrics classMetrics = (Metrics) classes.get(i);
            String cwmc = (classMetrics.getWmc() == -1d) ? "NULL" : "'"+Double.toString(classMetrics.getWmc())+"'";
            String cdit = (classMetrics.getDit() == -1d) ? "NULL" : "'"+Double.toString(classMetrics.getDit())+"'";
            String ccbo = (classMetrics.getCbo() == -1d) ? "NULL" : "'"+Double.toString(classMetrics.getCbo())+"'";
            String cnoc = (classMetrics.getNoc() == -1d) ? "NULL" : "'"+Double.toString(classMetrics.getNoc())+"'";
            String crfc = (classMetrics.getRfc() == -1d) ? "NULL" : "'"+Double.toString(classMetrics.getRfc())+"'";
            String clcom = (classMetrics.getLcom() == -1d) ? "NULL" : "'"+Double.toString(classMetrics.getLcom())+"'";
            String cfanin = (classMetrics.getFanin() == -1d) ? "NULL" : "'"+Double.toString(classMetrics.getFanin())+"'";
            String cfanout = (classMetrics.getFanout() == -1d) ? "NULL" : "'"+Double.toString(classMetrics.getFanout())+"'";
            String insert = "INSERT INTO class (name,wmc,dit,rfc,lcom,noc,cbo,fanin,release_id,project_id) VALUES ('"+this.escape(classMetrics.getName())+"',"
                +cwmc+","+cdit+","+crfc+","+clcom+","+cnoc+","+ccbo+","+cfanin+",'"+releaseID+"','"+projectID+"');";
            if(!(wmc.equals("NULL") || dit.equals("NULL") || cbo.equals("NULL")))
                exec.insert(insert);

        }

        return success;
    }


    private boolean insertRevisionData(RepositoryData data, String projectID, Matcher matcher) {
        boolean success = true;
        //first insert the retrieved revision data
        long loc = data.getLocs();
        int noocl = data.getNoOfClasses();
        ArrayList log = data.getRepositoryHistory();
        if(log == null || log.isEmpty())
            return false;
        SVNLogEntry head = (SVNLogEntry) log.get(log.size()-1);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");
        String date = escape(sdf.format(head.getDate()));
        int rno = (int) head.getRevision();

        //check the last stored revision
        String collaborator = (head.getAuthor() == null) ? "NULL" : "'"+escape(head.getAuthor())+"'";
        ArrayList<String> dbcommits = exec.query("SELECT MAX(revisionNo) FROM commit WHERE project_id = '"+projectID+"';");
        ArrayList<String> dbcollabs = exec.query("SELECT c.collaborator_id,c.user_id,c.alternative_id FROM collaborator c JOIN projectcollaborator pc ON pc.collaborator_id = c.collaborator_id WHERE pc.project_id = '"+projectID+"';");
        String collaboratorID = "NULL";
        if(head.getAuthor() != null && dbcollabs != null){
            //System.out.println("Matching "+dbcollabs+" with "+head.getAuthor()+" using matcher "+matcher);
            collaboratorID = matcher.matchCommitter(dbcollabs, head.getAuthor());
            collaboratorID = (collaboratorID.equals("-1") || collaborator.equals("NULL")) ? "NULL" : "'"+escape(collaboratorID)+"'";
        }
        int dbcommitno = 0;
        if(dbcommits != null && !dbcommits.get(0).toLowerCase().equals("null"))
            dbcommitno = Integer.parseInt(dbcommits.get(0));
        //if we retrieved a more recent revision, store it, else return (there will neither be more recent commits);
        if(rno > dbcommitno)
            success = success && exec.insert("INSERT INTO revision (project_id,revisionNo,date,loc,nocl,collaborator_id,collaborator) VALUES ('"+projectID+"','"+rno+"','"+date+"','"+loc+"','"+noocl+"',"+collaboratorID+",'"+escape(collaborator)+"');");
        else return true;
        //now process the commits and insert one by one if not already there
        for(int i = 0;i<log.size();i++){
            SVNLogEntry current = (SVNLogEntry)log.get(i);
            if(current.getRevision()> dbcommitno){
                ArrayList<SVNLogEntryPath> changes = new ArrayList<SVNLogEntryPath>(current.getChangedPaths().values());
                int lineschanged = changes.size();
                String comment ="";
                if(current.getMessage() == null || current.getMessage().equals("")){
                     comment = "NULL";
                }
                else{
                     comment = "'"+escape(current.getMessage())+"'";
                }

                String author = (current.getAuthor() == null) ? "NULL" : "'"+escape(current.getAuthor())+"'";

                String currentCollaboratorID = matcher.matchCommitter(dbcollabs, current.getAuthor());
                currentCollaboratorID = (currentCollaboratorID.equals("-1") || author.equals("NULL")) ? "NULL" : "'"+escape(currentCollaboratorID)+"'";
                exec.insert("INSERT INTO commit (project_id,collaborator,collaborator_id, revisionNo,date,linesChanged,comment) VALUES ('"+projectID+"',"+author+","+currentCollaboratorID+",'"+current.getRevision()+"','"+escape(sdf.format(current.getDate()))+"','"+lineschanged+"',"+comment+");");
                String commitID = exec.query("SELECT commit_id FROM commit WHERE project_id= '"+projectID+"' AND revisionNo='"+current.getRevision()+"';").get(0);

                //if successfull, insert all the paths that have been changed
                if(commitID != null && !commitID.toLowerCase().equals("null")){
                for(int j = 0;j<lineschanged;j++){
                    String path = changes.get(j).getPath();
                    char type = changes.get(j).getType();
                    exec.insert("INSERT INTO changedpath (commit_id,project_id,change,path) VALUES ('"+commitID+"','"+projectID+"','"+type+"','"+escape(path)+"');");
                }}
            }
        }


        return success;
    }


    private boolean insertCollaborators(ProjectInformation info,String projectID){

        boolean success = true;
        if(info == null)
            return false;

        String forge = (info.getForge() == Globals.GOOGLECODE) ? "googlecode" : "sourceforge";
        String requestQuery = "SELECT c.user_id, pc.project_id FROM collaborator c join projectcollaborator pc on c.collaborator_id = pc.collaborator_id WHERE forge='"+forge+"';";
        ArrayList<String> queries = new ArrayList<String>();

        for(int i = 0;i<info.getCollaboratorNo();i++){
            //first check if the collaborator is already in the DB
            String[] current = info.getCollaborator(i);

            String idQuery = "SELECT collaborator_id FROM collaborator WHERE user_id='"+current[0]+"' AND forge='"+forge+"';";
            ArrayList<String> col = exec.query(idQuery);
            //if it is not, add it and add the link
                if(col.isEmpty()){

                    String insertQuery = "INSERT INTO collaborator (user_id,forge,alternative_id) VALUES ('"+escape(current[0])+"','"+forge+"','"+escape(current[1])+"');";
                    success = success && exec.insert(insertQuery);
                    String id = exec.query(idQuery).get(0);
                    String linkQuery = "INSERT INTO projectcollaborator (project_id,collaborator_id) VALUES ('"+projectID+"','"+escape(id)+"')";
                    success = success && exec.insert(linkQuery);
                }
                else{
                    String id = col.get(0);
                    String testQuery = "SELECT collaborator_id,project_id FROM projectcollaborator WHERE collaborator_id='"+escape(id)+"' AND project_id='"+projectID+"';";
                    ArrayList<String> test = exec.query(testQuery);
                    if(test.isEmpty()){
                        String linkQuery = "INSERT INTO projectcollaborator (project_id,collaborator_id) VALUES ('"+projectID+"','"+escape(id)+"')";
                        success = success && exec.insert(linkQuery);
                    }
                }

        }
        //update the number of projects for that collaborator
        success = success && exec.insert("UPDATE collaborator c SET noprojects = n.number FROM (SELECT collaborator_id as id, count(project_id) as number FROM projectcollaborator GROUP BY collaborator_id) n WHERE c.collaborator_id = n.id;");

        return success;

    }

    private boolean insertBugs(BugData bugData, String pID, Matcher matcher) {
       boolean success = true;

       if(bugData == null || bugData.getBugList() == null)
           return false;

       ArrayList<String> dbbugs = exec.query("SELECT issueID from bug WHERE project_id = '"+pID+"'");
       ArrayList<String> dbcollabs = exec.query("SELECT c.collaborator_id,c.user_id,c.alternative_id FROM collaborator c JOIN projectcollaborator pc ON pc.collaborator_id = c.collaborator_id WHERE pc.project_id = '"+pID+"';");
       for(int i = 0;i<bugData.getBugList().size();i++){

            //first check the bugs already in the db
            Bug current = bugData.getBugList().get(i);

            //if the bug is not in the DB, add it
            if(!dbbugs.contains(current.getIssueID())){
                String issueID = (current.getIssueID() == null) ? "NULL" : "'"+escape(current.getIssueID())+"'";
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");
                String solvedDate = (current.getSolvedDate() == null) ? "NULL" : "'"+escape(sdf.format(current.getSolvedDate()))+"'";
                String description = (current.getDescription() == null) ? "NULL" : "'"+escape(current.getDescription())+"'";
                String author = (current.getAuthor() == null || current.getAuthor().toLowerCase().equals("null") || current.getAuthor().toLowerCase().equals("none")) ? "NULL" : "'"+escape(current.getAuthor())+"'";
                String owner = (current.getOwner() == null || current.getOwner().toLowerCase().equals("null") || current.getOwner().toLowerCase().equals("none")) ? "NULL" : "'"+escape(current.getOwner())+"'";
                String creationDate = (current.getCreationDate() == null) ? "NULL" : "'"+escape(sdf.format(current.getCreationDate()))+"'";
                String currentCollaboratorID = "NULL";
                if(!owner.equals("NULL")){
                    currentCollaboratorID = matcher.matchBugOwner(dbcollabs, current.getOwner());
                    currentCollaboratorID = (currentCollaboratorID.equals("-1") || current.getOwner().equals("NULL")) ? "NULL" : "'"+escape(currentCollaboratorID)+"'";
                }
                String insertQuery = ("INSERT INTO bug (issueID,status,creationDate,solvedDate,owner,owner_id,title,description,author,resolution,project_id) VALUES ("+issueID+",'"+escape(current.getStatus())+"',"+creationDate+","+solvedDate+",'"+escape(current.getOwner())+"',"+currentCollaboratorID+",'"+escape(current.getTitle())+"',"+description+","+author+",'"+escape(current.getResolution())+"','"+pID+"');").replace("'null'", "NULL");
                success = success && exec.insert(insertQuery);
            }
            //insert foreign keys
            //in any case, add the link to the new project
            //String linkQuery = "INSERT INTO projectcollaborator (project_id,collaborator_id) VALUES ('"+projectID+"','"+dbcollab.get(0)+"')";
            //success = success && exec.insert(linkQuery);

        }

        return success;
    }

    public static String escape(String toEscape){
        if(toEscape == null)
            return null;
        String escaped = toEscape.replaceAll("\\\\", "\\\\\\\\");
        escaped = escaped.replaceAll("'", "\\\\'");
        return escaped;
    }


}
